﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// MsSqlHelper 的摘要说明
/// </summary>
public class MsSqlHelper
{
    private SqlConnection conn;
    
    private string connection = ConfigurationManager.ConnectionStrings["mssqlConnectionString"].ToString();
    public MsSqlHelper()
    {
        Conn = new SqlConnection(connection);
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>  
    /// 执行不带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public int ExecuteSql(String Sqlstr)
    {
        using (SqlConnection conn = new SqlConnection(connection))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
            int num=Convert.ToInt16(cmd.ExecuteNonQuery());
            conn.Close();
            return (num == 0) ? 0 : num;
            //return 1;
        }
    }

    /// <summary>  
    /// 返回DataReader  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public SqlDataReader XExecuteReader(String Sqlstr)
    {
        using(SqlConnection conn = new SqlConnection(connection))
        {
            SqlCommand cmd = new SqlCommand(Sqlstr, conn);            
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            conn.Close();
                return dr;           
        }
    }
    public SqlDataReader ExecuteReader(String Sqlstr)
    {
        SqlConnection conn = new SqlConnection(connection);//返回DataReader时,是不可以用using()的  
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
            //sconn.Close();
        }
        catch //(Exception ex)  
        {
            return null;
        }
    }

    /// <summary>  
    /// 执行SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public DataTable ExecuteDt(String Sqlstr)
    {
        using (SqlConnection conn = new SqlConnection(connection))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
    }
    /// <summary>  
    /// 执行SQL语句并返回DataSet  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public DataSet ExecuteDs(String Sqlstr)
    {
        using (SqlConnection conn = new SqlConnection(connection))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }
    }
    /// <summary>
    /// 执行SQL语句并返回DataSet 
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="mssqlConnect">数据库链接</param>
    /// <returns></returns>
    public DataSet ExecuteDs(String Sqlstr, String mssqlConnect)
    {
        using (SqlConnection conn = new SqlConnection(mssqlConnect))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }
    }
    /// <summary>
    /// 执行SQL语句并返回一个文本值
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <returns></returns>
    public string GetString(string Sqlstr)
    {
        DataTable dt = ExecuteDt(Sqlstr);
        if (dt != null && dt.Rows.Count > 0)
        {
            return dt.Rows[0][0].ToString();
        }
        return "";
    }  

    public SqlConnection Conn
    {
        get
        {
            return conn;
        }

        set
        {
            conn = value;
        }
    }
}